package com.bcinfo.iccard.admin.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.bcinfo.iccard.model.Excelable;
import com.bcinfo.iccard.model.ToMerchantDaliyEarningsDetail;

// 读取Excel文件
public class ExcelUtil
{

    public static List<String> readExcelByFirstRow(String url) throws FileNotFoundException, IOException
    {
        File file = new File(url);
        List<String> result = new ArrayList<String>();
        if (file.getPath().endsWith(".xls"))
        {
            result = read2003ExcelByFirstRow(file);
        }
        else if (file.getPath().endsWith(".xlsx"))
        {
            result = read2007ExcelByFirstRow(file);
        }
        return result;
    }

    public static List<String[]> readExcel(File file) throws FileNotFoundException, IOException
    {
        List<String[]> result = new ArrayList<String[]>();
        if (file.getPath().endsWith(".xls"))
        {
            result = read2003Excel(file);
        }
        else if (file.getPath().endsWith(".xlsx"))
        {
            result = read2007Excel(file);
        }
        return result;
    }

    public static List<String> read2007ExcelByFirstRow(File file) throws FileNotFoundException, IOException
    {
        List<String> result = new ArrayList<String>();
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = xwb.getSheetAt(0);
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++)
        {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null)
            {
                continue;
            }
            // String[] rowArray = new String[sheet.getRow(0).getLastCellNum()];
            // for(short
            // columnIndex=0;columnIndex<row.getLastCellNum();columnIndex++){
            String value = "";
            XSSFCell cell = row.getCell(0);// 得到的每行的每个具体的值
            if (cell != null)
            {
                switch (cell.getCellType())
                {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                        if ("General".equals(cell.getCellStyle().getDataFormatString()))
                        {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        }
                        else
                        {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        // 导入时如果为公式生成的数据则无值
                        if (!cell.getStringCellValue().equals(""))
                        {
                            value = cell.getStringCellValue();
                        }
                        else
                        {
                            value = cell.getNumericCellValue() + "";
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        break;
                    case XSSFCell.CELL_TYPE_ERROR:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                        break;
                    default:
                        value = "";
                        break;
                }
                result.add(value);
            }
        }
        return result;
    }

    public static List<String[]> read2007Excel(File file) throws FileNotFoundException, IOException
    {
        List<String[]> result = new ArrayList<String[]>();
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = xwb.getSheetAt(0);
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++)
        {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null)
            {
                continue;
            }
            String[] rowArray = new String[sheet.getRow(0).getLastCellNum()];
            for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++)
            {
                String value = "";
                XSSFCell cell = row.getCell(columnIndex);// 得到的每行的每个具体的值
                if (cell != null)
                {
                    switch (cell.getCellType())
                    {
                        case XSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                            if ("General".equals(cell.getCellStyle().getDataFormatString()))
                            {
                                value = new DecimalFormat("0").format(cell.getNumericCellValue());
                            }
                            else
                            {
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                            }
                            break;
                        case XSSFCell.CELL_TYPE_FORMULA:
                            // 导入时如果为公式生成的数据则无值
                            if (!cell.getStringCellValue().equals(""))
                            {
                                value = cell.getStringCellValue();
                            }
                            else
                            {
                                value = cell.getNumericCellValue() + "";
                            }
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
                            break;
                        case XSSFCell.CELL_TYPE_ERROR:
                            value = "";
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
                            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                            break;
                        default:
                            value = "";
                            break;
                    }
                }
                rowArray[columnIndex] = value;
            }
            result.add(rowArray);
        }
        return result;
    }

    public static List<String> read2003ExcelByFirstRow(File file) throws FileNotFoundException, IOException
    {
        List<String> result = new ArrayList<String>();
        // 创建新的 excel工作簿
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = hwb.getSheetAt(0);
        // 循环每行
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++)
        {
            HSSFRow row = sheet.getRow(rowIndex);// 得到每一行的数据
            if (row == null)
            {
                continue;
            }
            // 循环每列
            // for (short columnIndex = 0; columnIndex <row.getLastCellNum();
            // columnIndex++) {
            String value = "";
            HSSFCell cell = row.getCell(0);
            if (cell != null)
            {
                switch (cell.getCellType())
                {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell))
                        {
                            Date date = cell.getDateCellValue();
                            if (date != null)
                            {
                                value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                            }
                            else
                            {
                                value = "";
                            }
                        }
                        else
                        {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        }
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        // 导入时如果为公式生成的数据则无值
                        if (!cell.getStringCellValue().equals(""))
                        {
                            value = cell.getStringCellValue();
                        }
                        else
                        {
                            value = cell.getNumericCellValue() + "";
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_ERROR:
                        value = "";
                        break;
                    default:
                        value = cell.toString();
                }
                // }
                result.add(value);
            }

        }
        return result;
    }

    public static List<String[]> read2003Excel(File file) throws FileNotFoundException, IOException
    {
        List<String[]> result = new ArrayList<String[]>();
        // 创建新的 excel工作簿
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = hwb.getSheetAt(0);
        // 循环每行
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++)
        {
            HSSFRow row = sheet.getRow(rowIndex);// 得到每一行的数据
            if (row == null)
            {
                continue;
            }
            String[] rowArray = new String[sheet.getRow(0).getLastCellNum()];
            // 循环每列
            for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++)
            {
                String value = "";
                HSSFCell cell = row.getCell(columnIndex);
                if (cell != null)
                {
                    switch (cell.getCellType())
                    {
                        case XSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell))
                            {
                                Date date = cell.getDateCellValue();
                                if (date != null)
                                {
                                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                }
                                else
                                {
                                    value = "";
                                }
                            }
                            else
                            {
                                value = new DecimalFormat("0").format(cell.getNumericCellValue());
                            }
                            break;
                        case XSSFCell.CELL_TYPE_FORMULA:
                            // 导入时如果为公式生成的数据则无值
                            if (!cell.getStringCellValue().equals(""))
                            {
                                value = cell.getStringCellValue();
                            }
                            else
                            {
                                value = cell.getNumericCellValue() + "";
                            }
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
                            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
                            value = "";
                            break;
                        case XSSFCell.CELL_TYPE_ERROR:
                            value = "";
                            break;
                        default:
                            value = cell.toString();
                    }
                }
                rowArray[columnIndex] = value;
            }
            result.add(rowArray);
        }
        return result;
    }

    @Deprecated
    public static String write(List<Excelable> objs, String dir) throws IOException
    {
        if (objs == null || objs.isEmpty())
            return "#";
        // 创建Excel的工作书册 Workbook,对应到一个excel文档
        HSSFWorkbook wb = new HSSFWorkbook();

        // 创建Excel的工作sheet,对应到一个excel文档的tab
        HSSFSheet sheet = wb.createSheet("sheet1");

        // 设置excel每列宽度
        // sheet.setColumnWidth(0, 4000);
        // sheet.setColumnWidth(1, 3500);

        // 创建字体样式
        HSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setBoldweight((short) 100);
        // font.setFontHeight((short) 300);
        font.setColor(HSSFColor.BLACK.index);
        //
        HSSFFont contentFont = wb.createFont();
        contentFont.setFontName("微软雅黑");
        contentFont.setColor(HSSFColor.BLACK.index);
        // 创建单元格样式
        HSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 设置边框
        titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        titleStyle.setFont(font);// 设置字体
        HSSFCellStyle contentStyle = wb.createCellStyle();
        contentStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        contentStyle.setFont(contentFont);// 设置字体
        // 创建Excel的sheet的标题行
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 500);// 设定行的高度
        // 创建一个Excel的单元格
        List<String> titles = objs.get(0).getTitles();
        int column = 0;
        for (String title : titles)
        {
            HSSFCell cell = row.createCell(column);
            sheet.autoSizeColumn(column, true);
            // 合并单元格(startRow，endRow，startColumn，endColumn)
            // sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

            // 给Excel的单元格设置样式和赋值
            cell.setCellStyle(titleStyle);
            cell.setCellValue(title);
            column++;
        }

        // 设置单元格内容格式
        HSSFCellStyle datetime = wb.createCellStyle();
        datetime.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));

        datetime.setWrapText(true);// 自动换行
        int rowNum = 1;
        for (Excelable obj : objs)
        {
            // ------------
            row = sheet.createRow(rowNum);
            column = 0;
            for (String title : titles)
            {
                // 设置单元格的样式格式
                HSSFCell cell = row.createCell(column);
                cell.setCellStyle(contentStyle);
                String val = obj.getValue(title);
                cell.setCellValue(val);
                sheet.autoSizeColumn(column, true);
                column++;
            }
            rowNum++;
        }

        String fileName = Thread.currentThread().getId() + "_" + System.currentTimeMillis() + ".xls";
        FileOutputStream os = new FileOutputStream(dir + fileName);
        wb.write(os);
        os.close();
        return fileName;
    }

    /**
     * 
     * 导出列表为excel文件 <br/>
     * <p>
     * Description: 导出列表为excel文件 ，导出的对象必须继承com.bcinfo.iccard.model.AbstractExcelable类<br/>
     * <p>
     * Author: arming<br/>
     * <p>
     * Date: 2013年12月28日-下午1:42:23<br/>
     * <p>
     * 
     * @param objs
     *            要导出的对象列表
     * @param dir
     *            导出文件存放的临时目录
     * @param alias
     *            导出的列.map<标题,属性>;顺序按放入map的顺序排列.
     * @return 文件名
     * @throws IOException
     * 
     */
    public static String write(List<Excelable> objs, String dir, Map<String, String> alias) throws IOException
    {
        if (objs == null || objs.isEmpty())
            return "#";
        // 创建Excel的工作书册 Workbook,对应到一个excel文档
        HSSFWorkbook wb = new HSSFWorkbook();

        // 创建Excel的工作sheet,对应到一个excel文档的tab
        HSSFSheet sheet = wb.createSheet("sheet1");

        // 设置excel每列宽度
        // sheet.setColumnWidth(0, 4000);
        // sheet.setColumnWidth(1, 3500);

        // 创建字体样式
        HSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setBoldweight((short) 100);
        // font.setFontHeight((short) 300);
        font.setColor(HSSFColor.BLACK.index);
        //
        HSSFFont contentFont = wb.createFont();
        contentFont.setFontName("微软雅黑");
        contentFont.setColor(HSSFColor.BLACK.index);
        // 创建单元格样式
        HSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 设置边框
        titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        titleStyle.setFont(font);// 设置字体
        HSSFCellStyle contentStyle = wb.createCellStyle();
        contentStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        contentStyle.setFont(contentFont);// 设置字体
        // 创建Excel的sheet的标题行
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 500);// 设定行的高度
        // 创建一个Excel的单元格
        Set<String> titles = alias.keySet();
        int column = 0;
        for (String title : titles)
        {
            HSSFCell cell = row.createCell(column);
            sheet.autoSizeColumn(column, true);
            // 合并单元格(startRow，endRow，startColumn，endColumn)
            // sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

            // 给Excel的单元格设置样式和赋值
            cell.setCellStyle(titleStyle);
            cell.setCellValue(title);
            column++;
        }

        // 设置单元格内容格式
        HSSFCellStyle datetime = wb.createCellStyle();
        datetime.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));

        datetime.setWrapText(true);// 自动换行
        int rowNum = 1;
        for (Excelable obj : objs)
        {
            obj.resetAlias(alias);
            // ------------
            row = sheet.createRow(rowNum);
            column = 0;
            for (String title : titles)
            {
                // 设置单元格的样式格式
                HSSFCell cell = row.createCell(column);
                cell.setCellStyle(contentStyle);
                String val = obj.getValue(title);
                cell.setCellValue(val);
                sheet.autoSizeColumn(column, true);
                column++;
            }
            rowNum++;
        }

        String fileName = Thread.currentThread().getId() + "_" + System.currentTimeMillis() + ".xls";
        FileOutputStream os = new FileOutputStream(dir + fileName);
        wb.write(os);
        os.close();
        return fileName;
    }

    /**
     * 删除临时文件 <br/>
     * <p>
     * <br/>
     * <p>
     * Author: arming<br/>
     * <p>
     * Date: 2013年12月28日-上午10:36:35<br/>
     * <p>
     * 
     * @param dir
     * 
     */
    public static void cleanTempFile(String dir)
    {
        if (StringUtils.isBlank(dir))
            return;
        File fd = new File(dir);
        File[] files = fd.listFiles();
        for (File f : files)
        {
            if (f.isFile() && (System.currentTimeMillis() - f.lastModified() > 600000))
            {
                try
                {
                    if (FilenameUtils.getExtension(f.getName()).toLowerCase().equals("xls"))
                        f.delete();
                }
                catch (Exception e)
                {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) throws IOException
    {
        List<Excelable> list = new ArrayList<Excelable>();
        ToMerchantDaliyEarningsDetail o = new ToMerchantDaliyEarningsDetail();
        o.setDaliyEarningsDate(new java.util.Date());
        o.setMerchantCode("100000-201");
        o.setMerchantName("测试商家111");
        o.setSettlementTotalAmount(10000.12f);
        list.add(o);

        System.out.println(ExcelUtil.write(list, "E:/"));
    }
}
